﻿USE [iKBSystem]
GO


--View_IMStatus-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[View_IMStatus]'))
DROP VIEW [dbo].[View_IMStatus]
GO

CREATE VIEW [dbo].[View_IMStatus]
AS
	SELECT
		ims.[Key],
		ims.[DomainScope] AS 'DomainScopeKey',
		ims.[DomainScopeUser] AS 'DomainScopeUserKey',
		ims.[Status] AS 'Status',
		ims.[StartStamp] AS 'StartStamp',
		ims.[EndStamp] AS 'EndStamp',
		scope.[Name] AS 'DomainScopeName',
		scopeUser.[ThirdPartyIdentity] AS 'DomainScopeUserThirdPartyIdentity'
		FROM [dbo].[IMStatus] ims
		LEFT JOIN [dbo].[DomainScope] scope ON scope.[Key]=ims.[DomainScope]
		LEFT JOIN [dbo].[DomainScopeUser] scopeUser ON scopeUser.[Key]=ims.[DomainScopeUser]
GO

--View_UserFullInfo-----------------
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[View_UserFullInfo]'))
DROP VIEW [dbo].[View_UserFullInfo]
GO

CREATE VIEW [dbo].[View_UserFullInfo]
AS
	
SELECT dsu.[Key] AS 'DomainScopeUserKey'
      ,dsu.[DomainScopeKey]
	  ,dsu.[PersonKey]
	  ,dsu.[Description]
	  ,dsu.[ThirdPartyIdentity]
	  ,dsu.[MonitorStatus]
	  ,dsu.[CreatedStamp]
	  ,dsu.[LastUpdatedStamp]
	  ,ds.[Name] AS 'DomainScopeName'
      ,ims2.[Status]
      ,ims2.[StartStamp]
      ,ims2.[EndStamp] 
      FROM [dbo].[DomainScopeUser] AS dsu	  
	  RIGHT JOIN [dbo].[DomainScope] AS ds
		ON ds.[Key]= dsu.[DomainScopeKey]
	  RIGHT JOIN dbo.[IMStatus] AS ims2
		ON dsu.[Key] = ims2.[DomainScopeUserKey]
	  RIGHT JOIN 
			(SELECT [DomainScopeUserKey] AS 'UserKey'
					,MAX([StartStamp]) AS 'LatestStamp'
				   FROM dbo.[IMStatus] AS ims
				GROUP BY ims.[DomainScopeUserKey]
			) AS R 
		ON R.UserKey= ims2.[DomainScopeUserKey] AND R.[LatestStamp] = ims2.[StartStamp];
GO